import type {
  AppointmentFilters,
  CreateAppointmentInput,
  ExcelImportResult,
  ExcelRowData,
  IssuingAuthority,
  IssuingCountry,
} from '@shared/types/'
import type { Buffer } from 'node:buffer'
import { readFile } from 'node:fs/promises'
import { normalizeAppointmentTime } from '@shared/utils/appointment-time'
import { generateEmail } from '@shared/utils/email'
import { generateChinaMobileNumber } from '@shared/utils/phone'
import dayjs from 'dayjs'
// Excel 导入服务
import * as XLSX from 'xlsx'
import { getLogger } from '../logger/index'
import { getAppointmentService } from './appointment-service'

export class ExcelImportService {
  private appointmentService = getAppointmentService()
  private logger = getLogger()

  // 将英文预约状态转换为中文
  private translateAppointmentStatus(status: string | undefined | null): string {
    if (!status)
      return ''
    const statusMap: Record<string, string> = {
      pending: '待处理',
      booked: '已预约',
      confirmed: '已确认',
      cancelled: '已取消',
      failed: '失败',
    }
    return statusMap[status.toLowerCase()] || status
  }

  // 验证签发国家
  private validateIssuingCountry(value: any): IssuingCountry {
    if (!value)
      return 'CN'
    const raw = String(value).trim()
    const up = raw.toUpperCase()
    // 支持英文代码及常见中文别名
    const CN_ALIASES_RAW = new Set(['中国内地', '中国', '中国大陆', '内地', '大陆', '中华人民共和国'])
    const CN_ALIASES_UP = new Set(['CN', 'CHINA'])
    const OTH_ALIASES_RAW = new Set(['其他', '其它', '海外', '国外', '香港', '澳门', '台湾'])
    const OTH_ALIASES_UP = new Set(['OTH'])
    if (CN_ALIASES_RAW.has(raw) || CN_ALIASES_UP.has(up))
      return 'CN'
    if (OTH_ALIASES_RAW.has(raw) || OTH_ALIASES_UP.has(up))
      return 'OTH'
    throw new Error(`无效的签发国家值: ${value}（支持 CN/OTH 或常见中文别名）`)
  }

  // 验证签发机关
  private validateIssuingAuthority(value: any, issuingCountry: IssuingCountry): IssuingAuthority {
    if (!value)
      return issuingCountry === 'CN' ? 'GD' : 'OTH'
    const raw = String(value).trim()
    const up = raw.toUpperCase()

    if (issuingCountry === 'CN') {
      const GD_ALIASES_RAW = new Set(['广东省', '广东'])
      const GD_ALIASES_UP = new Set(['GD'])
      const OTH_ALIASES_RAW = new Set(['其他', '其它', '省外'])
      const OTH_ALIASES_UP = new Set(['OTH'])
      if (GD_ALIASES_RAW.has(raw) || GD_ALIASES_UP.has(up))
        return 'GD'
      if (OTH_ALIASES_RAW.has(raw) || OTH_ALIASES_UP.has(up))
        return 'OTH'
      throw new Error(`签发国家为CN时，签发机关只能是 GD(广东) 或 OTH(其他): ${value}`)
    }
    else {
      const OTH_ALIASES_RAW = new Set(['其他', '其它', '海外', '国外'])
      const OTH_ALIASES_UP = new Set(['OTH'])
      if (OTH_ALIASES_RAW.has(raw) || OTH_ALIASES_UP.has(up))
        return 'OTH'
      throw new Error(`签发国家为OTH时，签发机关只能是 OTH(其他): ${value}`)
    }
  }

  // 验证布尔值
  private validateBoolean(value: any): boolean {
    if (typeof value === 'boolean')
      return value
    if (!value)
      return false
    const str = String(value).toLowerCase()
    if (['true', '是', '1', 'yes', 'y'].includes(str))
      return true
    if (['false', '否', '0', 'no', 'n'].includes(str))
      return false
    throw new Error(`无效的布尔值: ${value}`)
  }

  // 解析数组字段（逗号分隔）
  private parseArrayField(value: any): string[] | undefined {
    if (!value)
      return undefined
    const str = String(value).trim()
    if (!str)
      return undefined
    return str.split(/[,，]/).map(item => item.trim()).filter(item => item)
  }

  // 验证邮箱格式
  private validateEmail(email: string): boolean {
    const emailRegex = /^[^\s@]+@[^\s@][^\s.@]*\.[^\s@]+$/
    return emailRegex.test(email)
  }

  // 验证护照号格式
  private validatePassportNumber(passport: string): boolean {
    // 支持两类：
    // 1) 护照：1-2 个字母开头 + 6-14 位数字（如 E12345678）
    // 2) 香港身份证格式：1-2 个字母 + 6 位数字 + 校验位（0-9 或 A），括号可有可无（如 R783819(A) / R7838198）
    // 验证时忽略括号/空格等非字母数字字符，但入库保留原始（括号转半角、字母转大写）
    const normalized = passport.replace(/[^a-z0-9]/gi, '').toUpperCase()
    const passportRegex = /^[A-Z]{1,2}\d{6,14}$/
    const hkidRegex = /^[A-Z]{1,2}\d{6}[0-9A]$/
    return passportRegex.test(normalized) || hkidRegex.test(normalized)
  }

  // 验证驾照号格式
  private validateLicenseNumber(license: string): boolean {
    // 驾照号通常是15-20位数字或字母数字组合
    return license.length >= 15 && license.length <= 20
  }

  // 验证单行数据
  private validateRowData(rowData: Partial<ExcelRowData>, _rowIndex: number): {
    isValid: boolean
    errors: string[]
    processedData?: CreateAppointmentInput
  } {
    const errors: string[] = []
    const processedData: CreateAppointmentInput = {} as CreateAppointmentInput

    try {
      // 必填字段验证
      if (!rowData.护照号码) {
        errors.push('护照号码不能为空')
      }
      else {
        const rawPassport = String(rowData.护照号码).trim()
        if (!this.validatePassportNumber(rawPassport)) {
          errors.push('护照号码格式不正确')
        }
        else {
          // 保留括号与原始格式：将中文全角括号替换为英文半角括号，并将字母转为大写
          const storedPassport = rawPassport
            .replace(/（/g, '(')
            .replace(/）/g, ')')
            .replace(/[a-z]/g, c => c.toUpperCase())
          processedData.passport_number = storedPassport
        }
      }

      if (!rowData.驾照号码) {
        errors.push('驾照号码不能为空')
      }
      else {
        const license = String(rowData.驾照号码).trim()
        if (!this.validateLicenseNumber(license)) {
          errors.push('驾照号码格式不正确')
        }
        else {
          processedData.license_number = license
        }
      }

      if (!rowData.姓名) {
        errors.push('姓名不能为空')
      }
      else {
        processedData.full_name = String(rowData.姓名).trim()
      }

      if (!rowData.邮箱地址) {
        // 缺省邮箱：自动生成
        processedData.email = generateEmail()
      }
      else {
        const email = String(rowData.邮箱地址).trim()
        if (!this.validateEmail(email)) {
          errors.push('邮箱地址格式不正确')
        }
        else {
          processedData.email = email
        }
      }

      // 可选字段处理
      if (rowData.序号) {
        processedData.sequence_number = String(rowData.序号).trim()
      }

      if (rowData.电话号码) {
        processedData.phone_number = String(rowData.电话号码).trim()
      }
      else {
        // 缺省电话号码：生成中国大陆手机号
        processedData.phone_number = generateChinaMobileNumber()
      }

      // 签发国家和机关
      try {
        processedData.issuing_country = this.validateIssuingCountry(rowData.签发国家)
        processedData.issuing_authority = this.validateIssuingAuthority(
          rowData.签发机关,
          processedData.issuing_country,
        )
      }
      catch (e) {
        const msg = e instanceof Error ? e.message : String(e)
        errors.push(msg)
      }

      if (rowData.服务类型) {
        processedData.service_type = String(rowData.服务类型).trim()
      }
      else {
        // 缺省服务类型：默认 DI
        processedData.service_type = 'DI'
      }

      // 首选日期和时间（日期统一存为 YYYY-MM-DD）
      const preferredDatesRaw = this.parseArrayField((rowData as any).首选日期)
      if (preferredDatesRaw && preferredDatesRaw.length > 0) {
        const toYMD = (value: string): string => {
          const s = String(value).trim()
          if (!s)
            return s
          if (/^\d{4}-\d{2}-\d{2}$/.test(s))
            return s
          const m = s.match(/^(\d{2})-(\d{2})-(\d{4})$/)
          if (m) {
            const dd = m[1]
            const mm = m[2]
            const yyyy = m[3]
            return `${yyyy}-${mm}-${dd}`
          }
          return s
        }
        processedData.preferred_dates = preferredDatesRaw.map(toYMD)
      }
      processedData.preferred_times = this.parseArrayField((rowData as any).首选时间)

      // 优先级
      if (rowData.优先级) {
        const priority = Number(rowData.优先级)
        if (Number.isNaN(priority) || priority < 1 || priority > 5) {
          errors.push('优先级必须是1-5之间的数字')
        }
        else {
          processedData.priority = priority
        }
      }

      if (rowData.备注) {
        processedData.remarks = String(rowData.备注).trim()
      }

      // 是否强制使用首选日期
      try {
        const rawForce = (rowData as any)['是否强制使用首选日期']
        if (rawForce !== undefined)
          (processedData as any).force_preferred_dates = this.validateBoolean(rawForce)
      }
      catch {}

      // 是否占号数据（兼容从其它设备导出的Excel再导入）
      try {
        const rawPlaceholder = (rowData as any)['是否占号']
        if (rawPlaceholder !== undefined)
          processedData.is_placeholder = this.validateBoolean(rawPlaceholder)
      }
      catch {}

      // 不支持在模板中填写 UA（统一由系统生成或后续编辑回填）

      return {
        isValid: errors.length === 0,
        errors,
        processedData: errors.length === 0 ? processedData : undefined,
      }
    }
    catch (e) {
      const msg = e instanceof Error ? e.message : String(e)
      errors.push(`数据处理异常: ${msg}`)
      return { isValid: false, errors }
    }
  }

  // 读取Excel文件
  async importFromExcel(filePath: string): Promise<ExcelImportResult> {
    const result: ExcelImportResult = {
      success: false,
      total_rows: 0,
      success_count: 0,
      error_count: 0,
      errors: [],
      imported_ids: [],
    }

    try {
      this.logger.info('开始导入Excel文件:', filePath)

      // 读取文件
      const fileBuffer = await readFile(filePath)
      const workbook = XLSX.read(fileBuffer, { type: 'buffer' })

      // 获取第一个工作表
      const sheetName = workbook.SheetNames[0]
      if (!sheetName) {
        throw new Error('Excel文件中没有找到工作表')
      }

      const worksheet = workbook.Sheets[sheetName]

      // 转换为JSON数据
      const jsonData = XLSX.utils.sheet_to_json(worksheet, {
        header: 1, // 使用数组形式
        defval: '', // 空单元格默认值
      }) as any[][]

      if (jsonData.length < 2) {
        throw new Error('Excel文件中没有数据行')
      }

      // 获取表头
      const headers = jsonData[0] as string[]
      this.logger.info('Excel表头:', headers)

      // 验证必要列是否存在
      const requiredColumns = ['护照号码', '驾照号码', '姓名', '邮箱地址']
      const missingColumns = requiredColumns.filter(col => !headers.includes(col))
      if (missingColumns.length > 0) {
        throw new Error(`缺少必要列: ${missingColumns.join(', ')}`)
      }

      // 处理数据行
      const dataRows = jsonData.slice(1)
      result.total_rows = dataRows.length

      for (let i = 0; i < dataRows.length; i++) {
        const rowIndex = i + 2 // Excel行号（从2开始）
        const row = dataRows[i]

        try {
          // 构建行数据对象
          const rowData: Partial<ExcelRowData> = {}
          headers.forEach((header, index) => {
            if (header && row[index] !== undefined) {
              (rowData as any)[header] = row[index]
            }
          })

          // 验证行数据
          const validation = this.validateRowData(rowData, rowIndex)

          if (!validation.isValid) {
            result.error_count++
            // 收集错误并打印日志（包含行号与数据摘要）
            validation.errors.forEach((error) => {
              result.errors.push({
                row: rowIndex,
                message: error,
                data: rowData,
              })
            })
            try {
              this.logger.warn('Excel导入行校验失败', {
                row: rowIndex,
                errors: validation.errors,
                data: {
                  序号: (rowData as any)?.序号,
                  护照号码: (rowData as any)?.护照号码,
                  驾照号码: (rowData as any)?.驾照号码,
                  姓名: (rowData as any)?.姓名,
                  邮箱地址: (rowData as any)?.邮箱地址,
                  电话号码: (rowData as any)?.电话号码,
                  签发国家: (rowData as any)?.签发国家,
                  签发机关: (rowData as any)?.签发机关,
                  服务类型: (rowData as any)?.服务类型,
                },
              } as any)
            }
            catch {}
            continue
          }

          // 创建预约记录
          const appointment = await this.appointmentService.createAppointment(validation.processedData!)
          result.success_count++
          result.imported_ids.push(appointment.id)

          // 可选字段二次更新：兼容从其它设备导出的Excel再导入
          try {
            const updates: any = {}

            // 参考编号
            const ref = String((rowData as any)['参考编号'] || '').trim()
            if (ref)
              updates.reference_number = ref

            // 预约日期（支持 YYYY-MM-DD / DD-MM-YYYY；库字段使用 YYYY-MM-DD）
            const rawDate = String((rowData as any)['预约日期'] || '').trim()
            if (rawDate) {
              const toYMD = (value: string): string | undefined => {
                const s = String(value).trim()
                if (!s)
                  return undefined
                if (/^\d{4}-\d{2}-\d{2}$/.test(s))
                  return s
                const m = s.match(/^(\d{2})-(\d{2})-(\d{4})$/)
                if (m) {
                  const dd = m[1]
                  const mm = m[2]
                  const yyyy = m[3]
                  return `${yyyy}-${mm}-${dd}`
                }
                return s
              }
              const ymd = toYMD(rawDate)
              if (ymd)
                updates.appointment_date = ymd
            }

            // 预约时间（统一为 HH:MM 格式，移除秒部分）
            const rawTime = String((rowData as any)['预约时间'] || '').trim()
            if (rawTime) {
              updates.appointment_time = normalizeAppointmentTime(rawTime)
            }

            // 预约状态（中英文映射）
            const rawStatus = String((rowData as any)['预约状态'] || '').trim().toLowerCase()
            if (rawStatus) {
              const map: Record<string, string> = {
                pending: 'pending',
                待处理: 'pending',
                booked: 'booked',
                已预约: 'booked',
                confirmed: 'confirmed',
                已确认: 'confirmed',
                cancelled: 'cancelled',
                已取消: 'cancelled',
                failed: 'failed',
                失败: 'failed',
              }
              if (map[rawStatus] as any)
                updates.appointment_status = map[rawStatus]
            }

            // 预约成功时间（YYYY-MM-DD HH:mm:ss）
            const rawBookedAt = String((rowData as any)['预约成功时间'] || '').trim()
            if (rawBookedAt) {
              try {
                // 支持多种时间格式：YYYY-MM-DD HH:mm:ss、YYYY-MM-DD HH:mm、YYYY-MM-DD
                const t = dayjs(rawBookedAt)
                if (t.isValid())
                  updates.booked_at = t.millisecond(0).toDate()
                else
                  this.logger.warn(`导入第${rowIndex}行：预约成功时间格式无效: ${rawBookedAt}`)
              }
              catch (e) {
                this.logger.warn(`导入第${rowIndex}行：解析预约成功时间失败: ${rawBookedAt}`, e)
              }
            }

            // 创建时间（YYYY-MM-DD HH:mm:ss，用于保留原始创建时间）
            const rawCreatedAt = String((rowData as any)['创建时间'] || '').trim()
            if (rawCreatedAt) {
              try {
                // 支持多种时间格式：YYYY-MM-DD HH:mm:ss、YYYY-MM-DD HH:mm、YYYY-MM-DD
                const t = dayjs(rawCreatedAt)
                if (t.isValid())
                  updates.created_at = t.millisecond(0).toDate()
                else
                  this.logger.warn(`导入第${rowIndex}行：创建时间格式无效: ${rawCreatedAt}`)
              }
              catch (e) {
                this.logger.warn(`导入第${rowIndex}行：解析创建时间失败: ${rawCreatedAt}`, e)
              }
            }

            // 是否占号数据（兼容从其它设备导出的Excel再导入，确保占号状态不丢失）
            const rawPlaceholder = String((rowData as any)['是否占号'] || '').trim()
            if (rawPlaceholder) {
              try {
                updates.is_placeholder = this.validateBoolean(rawPlaceholder)
              }
              catch {}
            }

            if (Object.keys(updates).length > 0)
              await this.appointmentService.updateAppointment(appointment.id, updates)
          }
          catch {}

          this.logger.debug(`导入第${rowIndex}行成功: ${appointment.sequence_number}`)
        }
        catch (e) {
          result.error_count++
          result.errors.push({
            row: rowIndex,
            message: (e instanceof Error ? e.message : String(e)),
            data: row,
          })
          this.logger.warn(`导入第${rowIndex}行失败:`, (e instanceof Error ? e.message : String(e)))
        }
      }

      result.success = result.success_count > 0

      this.logger.info(`Excel导入完成: 总计${result.total_rows}行，成功${result.success_count}行，失败${result.error_count}行`)
      return result
    }
    catch (e) {
      this.logger.error('Excel导入失败:', e)
      result.errors.push({
        row: 0,
        message: `文件处理失败: ${e instanceof Error ? e.message : String(e)}`,
      })
      return result
    }
  }

  // 生成导入模板
  async generateTemplate(): Promise<Buffer> {
    try {
      // 创建示例数据
      const templateData = [
        [
          '序号',
          '护照号码',
          '驾照号码',
          '姓名',
          '邮箱地址',
          '电话号码',
          '签发国家',
          '签发机关',
          '服务类型',
          '首选日期',
          '首选时间',
          '是否强制使用首选日期',
          '优先级',
          '备注',
        ],
        [
          '20241201001',
          'E12345678',
          '440123199001011234',
          '张三',
          '(可留空自动生成)',
          '(可留空自动生成)',
          '中国内地',
          '广东省',
          '(可留空默认DI)',
          '25-11-2024,26-11-2024',
          '09:00,10:00',
          '否',
          '1',
          '测试数据',
        ],
        [
          '20241201002',
          'E87654321',
          '350123199002022345',
          '李四',
          '',
          '',
          '中国内地',
          '其他',
          '',
          '27-11-2024',
          '14:00,15:00',
          '否',
          '2',
          '',
        ],
        [
          '',
          'E11111111',
          '110123199003033456',
          '王五',
          '',
          '',
          '海外',
          '其他',
          '',
          '',
          '否',
          '',
          '3',
          '海外用户',
        ],
      ]

      // 创建工作簿
      const worksheet = XLSX.utils.aoa_to_sheet(templateData)
      const workbook = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(workbook, worksheet, '预约数据模板')

      // 设置列宽
      const colWidths = [
        { wch: 12 }, // 序号
        { wch: 12 }, // 护照号码
        { wch: 20 }, // 驾照号码
        { wch: 10 }, // 姓名
        { wch: 28 }, // 邮箱地址（可留空自动生成）
        { wch: 18 }, // 电话号码（可留空自动生成）
        { wch: 12 }, // 签发国家（支持中文：如 中国内地/海外）
        { wch: 12 }, // 签发机关（支持中文：如 广东省/其他）
        { wch: 14 }, // 服务类型（可留空默认 DI）
        { wch: 20 }, // 首选日期
        { wch: 15 }, // 首选时间
        { wch: 18 }, // 是否强制使用首选日期
        { wch: 8 }, // 优先级
        { wch: 15 }, // 备注
      ]
      worksheet['!cols'] = colWidths

      // 生成Excel文件缓冲区
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' })

      this.logger.info('Excel模板生成成功')
      return buffer
    }
    catch (error) {
      this.logger.error('生成Excel模板失败:', error)
      throw error
    }
  }

  // 导出预约数据到Excel
  async exportToExcel(appointmentIds?: number[]): Promise<Buffer> {
    try {
      // 获取数据
      let appointments
      if (appointmentIds?.length) {
        // 导出指定记录
        appointments = []
        for (const id of appointmentIds) {
          const appointment = await this.appointmentService.getAppointment(id)
          if (appointment) {
            appointments.push(appointment)
          }
        }
      }
      else {
        // 导出所有记录（分页聚合，突破单次 1000 条上限）
        const pageSize = 1000
        let page = 1
        let total = 0
        appointments = []
        while (true) {
          const result = await this.appointmentService.getAppointmentsList({}, { page, page_size: pageSize })
          if (page === 1)
            total = result.total
          if (!result.appointments.length)
            break
          appointments.push(...result.appointments)
          if (appointments.length >= total)
            break
          page++
        }
      }

      if (appointments.length === 0) {
        throw new Error('没有数据可导出')
      }

      // 转换数据格式
      const exportData = [
        [
          '序号',
          '护照号码',
          '驾照号码',
          '姓名',
          '邮箱地址',
          '电话号码',
          '签发国家',
          '签发机关',
          '服务类型',
          '首选日期',
          '首选时间',
          '是否强制使用首选日期',
          '优先级',
          '备注',
          '是否占号',
          '预约状态',
          '参考编号',
          '预约日期',
          '预约时间',
          '创建时间',
          '预约成功时间',
        ],
      ]

      appointments.forEach((appointment) => {
        exportData.push([
          appointment.sequence_number,
          appointment.passport_number,
          appointment.license_number,
          appointment.full_name,
          appointment.email,
          appointment.phone_number || '',
          appointment.issuing_country === 'CN' ? '中国内地' : '海外',
          appointment.issuing_authority === 'GD' ? '广东省' : '其他',
          appointment.service_type,
          appointment.preferred_dates ? JSON.parse(appointment.preferred_dates).join(',') : '',
          appointment.preferred_times ? JSON.parse(appointment.preferred_times).join(',') : '',
          appointment.force_preferred_dates ? '是' : '否',
          appointment.priority,
          appointment.remarks || '',
          appointment.is_placeholder ? '是' : '否',
          this.translateAppointmentStatus(appointment.appointment_status),
          appointment.reference_number || '',
          // 预约日期统一 YYYY-MM-DD（原存储可能为 DD-MM-YYYY）
          (function formatDate(d: string | undefined) {
            const s = String(d || '')
            if (/^\d{2}-\d{2}-\d{4}$/.test(s)) {
              const [dd, mm, yyyy] = s.split('-')
              return `${yyyy}-${mm}-${dd}`
            }
            return s
          })(appointment.appointment_date || ''),
          normalizeAppointmentTime(appointment.appointment_time || ''),
          appointment.created_at ? dayjs(appointment.created_at).format('YYYY-MM-DD HH:mm:ss') : '',
          appointment.booked_at ? dayjs(appointment.booked_at).format('YYYY-MM-DD HH:mm:ss') : '',
        ])
      })

      // 创建工作簿
      const worksheet = XLSX.utils.aoa_to_sheet(exportData)
      const workbook = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(workbook, worksheet, '预约数据')

      // 生成Excel文件缓冲区
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' })

      this.logger.info(`导出${appointments.length}条预约记录到Excel`)
      return buffer
    }
    catch (error) {
      this.logger.error('导出Excel失败:', error)
      throw error
    }
  }

  // 按筛选条件导出全部预约数据到Excel
  async exportByFilters(filters: AppointmentFilters = {}): Promise<Buffer> {
    try {
      // 分页聚合
      const pageSize = 1000
      let page = 1
      let total = 0
      const all: any[] = []
      while (true) {
        const result = await this.appointmentService.getAppointmentsList(filters, { page, page_size: pageSize })
        if (page === 1)
          total = result.total
        if (!result.appointments.length)
          break
        all.push(...result.appointments)
        if (all.length >= total)
          break
        page++
      }

      if (all.length === 0)
        throw new Error('没有数据可导出')

      const exportData = [
        [
          '序号',
          '护照号码',
          '驾照号码',
          '姓名',
          '邮箱地址',
          '电话号码',
          '签发国家',
          '签发机关',
          '服务类型',
          '首选日期',
          '首选时间',
          '是否强制使用首选日期',
          '优先级',
          '备注',
          '是否占号',
          '预约状态',
          '参考编号',
          '预约日期',
          '预约时间',
          '创建时间',
          '预约成功时间',
        ],
      ]

      all.forEach((appointment) => {
        exportData.push([
          appointment.sequence_number,
          appointment.passport_number,
          appointment.license_number,
          appointment.full_name,
          appointment.email,
          appointment.phone_number || '',
          appointment.issuing_country === 'CN' ? '中国内地' : '海外',
          appointment.issuing_authority === 'GD' ? '广东省' : '其他',
          appointment.service_type,
          appointment.preferred_dates ? JSON.parse(appointment.preferred_dates).join(',') : '',
          appointment.preferred_times ? JSON.parse(appointment.preferred_times).join(',') : '',
          appointment.force_preferred_dates ? '是' : '否',
          appointment.priority,
          appointment.remarks || '',
          appointment.is_placeholder ? '是' : '否',
          this.translateAppointmentStatus(appointment.appointment_status),
          appointment.reference_number || '',
          (function formatDate(d: string | undefined) {
            const s = String(d || '')
            if (/^\d{2}-\d{2}-\d{4}$/.test(s)) {
              const [dd, mm, yyyy] = s.split('-')
              return `${yyyy}-${mm}-${dd}`
            }
            return s
          })(appointment.appointment_date || ''),
          normalizeAppointmentTime(appointment.appointment_time || ''),
          appointment.created_at ? dayjs(appointment.created_at).format('YYYY-MM-DD HH:mm:ss') : '',
          appointment.booked_at ? dayjs(appointment.booked_at).format('YYYY-MM-DD HH:mm:ss') : '',
        ])
      })

      const worksheet = XLSX.utils.aoa_to_sheet(exportData)
      const workbook = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(workbook, worksheet, '预约数据')
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' })
      this.logger.info(`按筛选导出${all.length}条预约记录到Excel`)
      return buffer
    }
    catch (error) {
      this.logger.error('按筛选导出Excel失败:', error)
      throw error
    }
  }
}

// 创建全局服务实例
let excelImportService: ExcelImportService | null = null

export function getExcelImportService(): ExcelImportService {
  if (!excelImportService) {
    excelImportService = new ExcelImportService()
  }
  return excelImportService
}
